package org.jeecg.modules.demo.mp.util;

import org.apache.poi.ss.usermodel.*;
import org.jeecg.modules.demo.mp.entity.MpBudget;
import org.jeecg.modules.demo.mp.entity.MpMasterplan;
import org.jeecgframework.poi.excel.entity.ImportParams;
import org.jeecgframework.poi.exception.excel.ExcelImportException;
import org.jeecgframework.poi.exception.excel.enums.ExcelImportEnum;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel解析
 */
public class MPParser {
    private static final Logger LOGGER = LoggerFactory.getLogger(MPParser.class);

    public static List<MpBudget> parseBudget(File file, String currVersion) {
        List result = null;
        FileInputStream inputstream = null;
        try {
            inputstream = new FileInputStream(file);
            result = parseBudget(inputstream, currVersion);
        } catch (Exception var14) {
            LOGGER.error(var14.getMessage(), var14);
        } finally {
            try {
                inputstream.close();
            } catch (IOException var13) {
                LOGGER.error(var13.getMessage(), var13);
            }
        }

        return result;
    }

    public static List<MpBudget> parseBudget(InputStream inputstream, String currVersion) throws Exception {
        ImportParams params = new ImportParams();

        List<MpBudget> list = new ArrayList();
        Workbook book = null;
        boolean isXSSFWorkbook = false;
        if (!((InputStream) inputstream).markSupported()) {
            inputstream = new PushbackInputStream((InputStream) inputstream, 8);
        }

        book = WorkbookFactory.create((InputStream) inputstream);

        String sheet_budget = "Budget from client";
        Sheet sheet = book.getSheet(sheet_budget);

        Object object = null;


        Map<String, Integer> titleMap = new HashMap<String, Integer>();

        int headRow = 6;
        Row row = sheet.getRow(headRow);
        for (int i = 0; i < row.getLastCellNum(); i++) {
            titleMap.put(getCellValue4String(row.getCell(i)), i);
        }


        int skip = 0;
        MpBudget obj = null;
        for (int rowIndex = headRow + 1; rowIndex < sheet.getLastRowNum(); rowIndex++) {
            row = sheet.getRow(rowIndex);
            if (row == null) {
                skip++;

                if (skip > 5) {
                    //  最多支持连续5个空行
                    break;
                }
                continue;
            } else {
                skip = 0;
            }

            try {
                obj = new MpBudget();
                obj.setBuyingType(getCellValue4String(row.getCell(titleMap.get("Buying Type"))));
                obj.setDivision(getCellValue4String(row.getCell(titleMap.get("Division"))));
                obj.setDetailedDivision(getCellValue4String(row.getCell(titleMap.get("Detailed Division"))));
                obj.setProduct(getCellValue4String(row.getCell(titleMap.get("Product"))));
                obj.setWave(getCellValue4String(row.getCell(titleMap.get("Wave"))));
                obj.setCampaignName(getCellValue4String(row.getCell(titleMap.get("Campaign Name"))));
                obj.setMediaType(getCellValue4String(row.getCell(titleMap.get("MediaType"))));
                obj.setBudget(getCellValue4BigDecimal(row.getCell(titleMap.get(currVersion))));

                if (isNull(obj.getDivision()) || isNull(obj.getProduct())) {
                    skip++;

                    if (skip > 5) {
                        //  最多支持连续5个空行
                        break;
                    }
                    continue;
                }

                list.add(obj);

            } catch (ExcelImportException var27) {
                if (!var27.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {
                    throw new ExcelImportException(var27.getType(), var27);
                }
            }
        }


        return list;

    }


    public static List<MpMasterplan> parseMasterPlan(InputStream inputstream, String currVersion) throws Exception {
        ImportParams params = new ImportParams();

        List<MpMasterplan> list = new ArrayList();
        Workbook book = null;
        boolean isXSSFWorkbook = false;
        if (!((InputStream) inputstream).markSupported()) {
            inputstream = new PushbackInputStream((InputStream) inputstream, 8);
        }

        book = WorkbookFactory.create((InputStream) inputstream);

        String sheet_budget = "Digital&OOH Master Media Plan";
        Sheet sheet = book.getSheet(sheet_budget);

        Object object = null;


        Map<String, Integer> titleMap = new HashMap<String, Integer>();

        int headRow = 6;
        Row row = sheet.getRow(headRow);
        for (int i = 0; i < row.getLastCellNum(); i++) {
            titleMap.put(getCellValue4String(row.getCell(i)), i);
        }


        Map<String, String> monthNameMap = new HashMap<String, String>();
        monthNameMap.put("Jan", "01");
        monthNameMap.put("Feb", "02");
        monthNameMap.put("Mar", "03");
        monthNameMap.put("Apr", "04");
        monthNameMap.put("May", "05");
        monthNameMap.put("Jun", "06");
        monthNameMap.put("Jul", "07");
        monthNameMap.put("Aug", "08");
        monthNameMap.put("Sep", "09");
        monthNameMap.put("Oct", "10");
        monthNameMap.put("Nov", "11");
        monthNameMap.put("Dec", "12");

        int skip = 0;
        MpMasterplan obj = null;
        for (int rowIndex = headRow + 1; rowIndex < sheet.getLastRowNum(); rowIndex++) {
            row = sheet.getRow(rowIndex);
            if (row == null) {
                skip++;

                if (skip > 5) {
                    //  最多支持连续5个空行
                    break;
                }
                continue;
            } else {
                skip = 0;
            }

            try {
                obj = new MpMasterplan();
                String month = getCellValue4String(row.getCell(titleMap.get("Month")));
                String year = getCellValue4String(row.getCell(titleMap.get("Year")));
                if(year!=null && year.length()>4){
                    year = year.substring(0,4);
                }
                obj.setVersion(year + monthNameMap.get(month));
                obj.setBuyingType(getCellValue4String(row.getCell(titleMap.get("Buying Type"))));
                obj.setStatu(getCellValue4String(row.getCell(titleMap.get("Status"))));
                obj.setDivision(getCellValue4String(row.getCell(titleMap.get("Division"))));
                obj.setDetailedDivision(getCellValue4String(row.getCell(titleMap.get("Detailed Division"))));
                obj.setProduct(getCellValue4String(row.getCell(titleMap.get("Product"))));


                if (isNull(obj.getDivision()) || isNull(obj.getProduct())) {
                    skip++;

                    if (skip > 5) {
                        //  最多支持连续5个空行
                        break;
                    }
                    continue;
                }


                obj.setWave(getCellValue4String(row.getCell(titleMap.get("Wave"))));
                obj.setCampaignName(getCellValue4String(row.getCell(titleMap.get("Campaign Name"))));
                obj.setMediaType(getCellValue4String(row.getCell(titleMap.get("MediaType"))));
                obj.setMedia(getCellValue4String(row.getCell(titleMap.get("Media"))));
                obj.setMediaGroup(getCellValue4String(row.getCell(titleMap.get("Media Group"))));
                obj.setMarketRegion(getCellValue4String(row.getCell(titleMap.get("Market Region"))));
                obj.setForOmg(getCellValue4String(row.getCell(titleMap.get("DID回传"))));
                obj.setTechCost(getCellValue4BigDecimal(row.getCell(titleMap.get("Technical Cost"))));
                obj.setMediaSpending(getCellValue4BigDecimal(row.getCell(titleMap.get("Media Spending"))));
                obj.setAc(getCellValue4BigDecimal(row.getCell(titleMap.get("AC"))));


                list.add(obj);

            } catch (ExcelImportException var27) {
                if (!var27.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {
                    throw new ExcelImportException(var27.getType(), var27);
                }
            }
        }


        return list;

    }


    public static boolean isNull(Object obj) {

        if (obj == null) {
            return true;
        } else if (obj instanceof String) {
            return ((String) obj).trim().length() == 0;
        } else if (obj instanceof BigDecimal) {
            return ((BigDecimal) obj).doubleValue() == 0.0;
        }

        return false;
    }

    public static String getCellValue4String(Cell cell) {
        String r = null;
        if (cell != null) {
            Object result = null;
            if (CellType.NUMERIC == cell.getCellTypeEnum()) {
                result = cell.getNumericCellValue();
            } else if (CellType.BOOLEAN == cell.getCellTypeEnum()) {
                result = cell.getBooleanCellValue();
//            } else if (CellType.FORMULA == cell.getCellTypeEnum() ) {
//                result = cell.getNumericCellValue();
            } else {
                result = cell.getStringCellValue();
            }
//            r = cell.getStringCellValue();
            if (result != null) {
                r = result.toString();
            }
        }
        return r;
    }

    public static BigDecimal getCellValue4BigDecimal(Cell cell) {
        BigDecimal r = null;
        if (cell != null) {
            r = new BigDecimal(cell.getNumericCellValue());
        }
        return r;
    }

}
